package org.cjlm.dao;

import org.cjlm.MLocator;
import org.cjlm.itf.ConnectionProvider;
import org.cjlm.vo.SuperVO;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.function.Consumer;
import java.util.function.Function;

public class SuperDao {
    // ======================== 事务模板方法 ========================
    private <T> T executeTransaction(Function<Connection, T> action) {
        Connection conn = null;
        try {
            conn = getConn();
            conn.setAutoCommit(false);
            T result = action.apply(conn);
            conn.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(conn);
            throw new RuntimeException("Database operation failed: " + e.getMessage(),  e);
        } finally {
            closeQuietly(conn);
        }
    }

    private void executeTransactionVoid(Consumer<Connection> action) {
        executeTransaction(conn -> {
            action.accept(conn);
            return null;
        });
    }

    // ======================== 核心操作方法 ========================
    public <T extends SuperVO> T insert(T vo) {
        return executeTransaction(conn -> {
            insertRecursive(conn, vo, null);
            return vo;
        });
    }

    public <T extends SuperVO> T update(T vo) {
        executeTransactionVoid(conn -> {
            updateNode(conn, vo);
            if (vo.getChildren()  != null) {
                vo.getChildren().forEach(child  -> updateRecursive(conn, child, vo));
            }
            handleDeletedChildren(conn, vo);
        });
        return vo;
    }

    public <T extends SuperVO> T delete(T vo) {
        executeTransactionVoid(conn -> {
            if (vo.getChildren()  != null) {
                vo.getChildren().forEach(child  -> deleteRecursive(conn, child));
            }
            deleteNode(conn, vo);
        });
        return vo;
    }

    // ======================== 查询方法 ========================
    public <T extends SuperVO> List<T> query(T queryvo) {
        return executeQuery(conn -> {
            List<T> result = queryByCondition(conn, queryvo);
            result.forEach(vo  -> fillChildrenRecursive(conn, vo));
            return result;
        });
    }

    public <T extends SuperVO> List<T> query(T queryvo, String orderPart) {
        return executeQuery(conn -> {
            List<T> result = queryByConditionWithOrder(conn, queryvo, orderPart);
            result.forEach(vo  -> fillChildrenRecursive(conn, vo));
            return result;
        });
    }

    public <T extends SuperVO> T queryById(T queryvo) {
        return executeQuery(conn -> {
            T result = querySingleNode(conn, queryvo);
            if (result != null) fillChildrenRecursive(conn, result);
            return result;
        });
    }

    // ======================== 查询辅助方法 ========================
    private <T> T executeQuery(Function<Connection, T> action) {
        try (Connection conn = getConn()) {
            return action.apply(conn);
        } catch (SQLException e) {
            throw new RuntimeException("Query failed: " + e.getMessage(),  e);
        }
    }

    private <T extends SuperVO> List<T> queryByCondition(Connection conn, T condition) {

        return buildAndExecuteQuery(conn, condition, null);
    }

    private <T extends SuperVO> List<T> queryByConditionWithOrder(
            Connection conn, T condition, String orderPart) {

        return buildAndExecuteQuery(conn, condition, orderPart);
    }

    // ======================== 统一的查询构建器 ========================
    private <T extends SuperVO> List<T> buildAndExecuteQuery(Connection conn, T condition, String orderPart) {
        List<T> result = new ArrayList<>();
        try {
            Class<T> voClass = (Class<T>) condition.getClass();
            StringBuilder sql = new StringBuilder("SELECT * FROM ")
                    .append(condition.getTableName())
                    .append(" WHERE 1=1");

            List<Object> params = new ArrayList<>();
            for (Field field : getConditionFields(condition)) {
                field.setAccessible(true);
                Object value = field.get(condition);

                if (value != null) {
                    sql.append("  AND ").append(field.getName()).append("  = ?");
                    params.add(value);
                }
            }

            if (orderPart != null && !orderPart.trim().isEmpty())  {
                sql.append("  ORDER BY ").append(orderPart);
            }

            try (PreparedStatement stmt = conn.prepareStatement(sql.toString()))  {
                for (int i = 0; i < params.size();  i++) {
                    stmt.setObject(i  + 1, params.get(i));
                }
                try (ResultSet rs = stmt.executeQuery())  {
                    while (rs.next())  {
                        T vo = voClass.getDeclaredConstructor().newInstance();
                        mapResultSetToVO(rs, vo);
                        result.add(vo);
                    }
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } catch (InvocationTargetException e) {
                throw new RuntimeException(e);
            } catch (InstantiationException e) {
                throw new RuntimeException(e);
            } catch (NoSuchMethodException e) {
                throw new RuntimeException(e);
            }
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
        return result;
    }

    // ======================== 递归操作框架 ========================
    private void processRecursively(
            Connection conn,
            SuperVO parent,
            Consumer<SuperVO> nodeProcessor,
            Consumer<SuperVO> childProcessor) throws ReflectiveOperationException, SQLException {

        nodeProcessor.accept(parent);
        if (parent.getChildren()  != null) {
            for (SuperVO child : parent.getChildren())  {
                childProcessor.accept(child);
                processRecursively(conn, child, nodeProcessor, childProcessor);
            }
        }
    }

    private void insertRecursive(Connection conn, SuperVO vo, Object parentId) {

        try {
            setParentId(vo, parentId);
            executeInsert(conn, vo);
            if (vo.getChildren()  != null) {
                for (SuperVO child : vo.getChildren())  {
                    insertRecursive(conn, child, vo.getPrimaryKeyValue());
                }
            }
        } catch (ReflectiveOperationException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private void updateRecursive(Connection conn, SuperVO child, SuperVO parent) {

        setParentId(child, parent.getPrimaryKeyValue());
        if (isNewEntity(child)) {
            insertRecursive(conn, child, parent.getPrimaryKeyValue());
        } else {
            updateNode(conn, child);
        }
        handleDeletedChildren(conn, child);
    }

    // ======================== 字段操作工具 ========================
    private List<Field> getAllFields(Class<?> clazz) {
        List<Field> fields = new ArrayList<>();
        while (clazz != null && clazz != Object.class)  {
            Collections.addAll(fields,  clazz.getDeclaredFields());
            clazz = clazz.getSuperclass();
        }
        return fields;
    }

    private <T extends SuperVO> List<Field> getConditionFields(T vo) {
        List<Field> fields = new ArrayList<>();
        for (Field field : getAllFields(vo.getClass()))  {
            if (!"children".equals(field.getName())  &&
                    !vo.getPrimarykeyName().equals(field.getName()))  {
                fields.add(field);
            }
        }
        return fields;
    }

    private List<Field> getInsertFields(SuperVO vo) {
        List<Field> fields = new ArrayList<>();
        for (Field field : getAllFields(vo.getClass()))  {
            if (!"children".equals(field.getName()))  {
                fields.add(field);
            }
        }
        return fields;
    }

    private List<Field> getUpdateFields(SuperVO vo) {
        List<Field> fields = new ArrayList<>();
        for (Field field : getAllFields(vo.getClass()))  {
            if (!"children".equals(field.getName())  &&
                    !field.getName().equals(vo.getPrimarykeyName()))  {
                fields.add(field);
            }
        }
        return fields;
    }

    // ======================== SQL操作工具 ========================
    private void executeInsert(Connection conn, SuperVO vo)
            throws SQLException, ReflectiveOperationException {

        String sql = buildInsertSQL(vo);
        try (PreparedStatement stmt = buildStatement(conn, vo, sql)) {
            stmt.executeUpdate();
            handleGeneratedKeys(conn, vo, stmt);
        }
    }

    private void updateNode(Connection conn, SuperVO vo) {

        String sql = null;
        try {
            sql = buildUpdateSQL(vo);
            try (PreparedStatement stmt = buildUpdateStatement(conn, vo, sql)) {
                stmt.executeUpdate();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        } catch (ReflectiveOperationException e) {
            throw new RuntimeException(e);
        }

    }

    private void fillChildrenRecursive(Connection conn, SuperVO parent){
        String sql = "SELECT * FROM " + parent.getTableName()
                + " WHERE " + parent.getParentName()  + " = ?";

        try (PreparedStatement stmt = conn.prepareStatement(sql))  {
            stmt.setObject(1,  parent.getPrimaryKeyValue());

            try (ResultSet rs = stmt.executeQuery())  {
                List<SuperVO> children = new ArrayList<>();
                while (rs.next())  {
                    // 动态创建子节点实例
                    SuperVO child = parent.getClass().getDeclaredConstructor().newInstance();
                    mapResultSetToVO(rs, child);
                    children.add(child);
                    // 递归填充孙节点
                    fillChildrenRecursive(conn, child);
                }
                parent.setChildren(children);
            } catch (InvocationTargetException e) {
                throw new RuntimeException(e);
            } catch (InstantiationException e) {
                throw new RuntimeException(e);
            } catch (NoSuchMethodException e) {
                throw new RuntimeException(e);
            }
        } catch (SQLException | IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }

    private <T extends SuperVO> T querySingleNode(Connection conn, T queryvo){

        String sql = "SELECT * FROM " + queryvo.getTableName() + " WHERE " + queryvo.getPrimarykeyName() + " = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, queryvo.getPrimaryKeyValue());
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    mapResultSetToVO(rs, queryvo); // 映射结果集到VO对象
                    return queryvo;
                }
            }
        } catch (SQLException | IllegalAccessException e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    protected <T extends SuperVO> void mapResultSetToVO(ResultSet rs, T vo)
            throws SQLException, IllegalAccessException {
        for (Field field : getAllFields(vo.getClass())) {
            if ("children".equals(field.getName())) continue;
            field.setAccessible(true);
            Object value = rs.getObject(field.getName());
            if (value != null) {
                field.set(vo, convertType(value, field.getType()));
            }
        }
    }

    private void deleteRecursive(Connection conn, SuperVO vo) {

        // 先删除子节点
        List<SuperVO> children = vo.getChildren();
        if (children != null) {
            for (SuperVO child : children) {
                deleteRecursive(conn, child);
            }
        }

        // 再删除当前节点
        deleteNode(conn, vo);
    }

    private void deleteNode(Connection conn, SuperVO vo){

        String sql = "DELETE FROM " + vo.getTableName() + " WHERE " + vo.getPrimarykeyName() + " = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            Field pkField = findField(vo.getClass(), vo.getPrimarykeyName());
            pkField.setAccessible(true);
            stmt.setObject(1, pkField.get(vo));
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (NoSuchFieldException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }

    private void handleDeletedChildren(Connection conn, SuperVO parent){

        Set<Object> currentChildIds = null;
        try {
            currentChildIds = getCurrentChildIds(parent);
            Set<Object> existingChildIds = fetchExistingChildIds(conn, parent);

            // 找出需要删除的ID
            existingChildIds.removeAll(currentChildIds);

            // 执行删除
            for (Object idToDelete : existingChildIds) {
                SuperVO temp = createTempVO(parent.getClass());
                Field pkField = findField(temp.getClass(), temp.getPrimarykeyName());
                pkField.setAccessible(true);
                pkField.set(temp, idToDelete);
                deleteRecursive(conn, temp);
            }
        } catch (ReflectiveOperationException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private Set<Object> getCurrentChildIds(SuperVO parent)
            throws ReflectiveOperationException {

        Set<Object> ids = new HashSet<>();
        List<SuperVO> children = parent.getChildren();
        if (children != null) {
            for (SuperVO child : children) {
                Field pkField = findField(child.getClass(), child.getPrimarykeyName());
                pkField.setAccessible(true);
                Object id = pkField.get(child);
                if (id != null) {
                    ids.add(id);
                }
            }
        }
        return ids;
    }

    private Set<Object> fetchExistingChildIds(Connection conn, SuperVO parent)
            throws SQLException, ReflectiveOperationException {

        Set<Object> ids = new HashSet<>();
        String sql = "SELECT " + parent.getPrimarykeyName() + " FROM " + parent.getTableName() +
                " WHERE " + parent.getParentName() + " = ?";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            Field pkField = findField(parent.getClass(), parent.getPrimarykeyName());
            pkField.setAccessible(true);
            Object parentId = pkField.get(parent);
            stmt.setObject(1, parentId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    ids.add(rs.getObject(1));
                }
            }
        }
        return ids;
    }

    private String buildInsertSQL(SuperVO vo) throws ReflectiveOperationException {
        List<Field> fields = getInsertFields(vo);
        StringBuilder sql = new StringBuilder("INSERT INTO ")
                .append(vo.getTableName())
                .append(" (");

        for (int i = 0; i < fields.size(); i++) {
            if (i > 0) sql.append(",  ");
            sql.append(fields.get(i).getName());
        }

        sql.append(")  VALUES (");
        for (int i = 0; i < fields.size(); i++) {
            if (i > 0) sql.append(",  ");
            sql.append("?");
        }
        sql.append(")");

        return sql.toString();
    }

    private PreparedStatement buildStatement(Connection conn, SuperVO vo, String sql)
            throws SQLException, ReflectiveOperationException {

        List<Field> fields = getInsertFields(vo);
        PreparedStatement stmt = conn.prepareStatement(
                sql,
                PreparedStatement.RETURN_GENERATED_KEYS
        );

        for (int i = 0; i < fields.size(); i++) {
            Field field = fields.get(i);
            field.setAccessible(true);
            stmt.setObject(i + 1, field.get(vo));
        }

        return stmt;
    }

    private String buildUpdateSQL(SuperVO vo) throws ReflectiveOperationException {
        List<Field> fields = getUpdateFields(vo);
        StringBuilder sql = new StringBuilder("UPDATE ")
                .append(vo.getTableName())
                .append(" SET ");

        for (int i = 0; i < fields.size(); i++) {
            if (i > 0) sql.append(",  ");
            sql.append(fields.get(i).getName()).append("  = ?");
        }

        sql.append("  WHERE ").append(vo.getPrimarykeyName()).append("  = ?");
        return sql.toString();
    }

    private PreparedStatement buildUpdateStatement(Connection conn, SuperVO vo, String sql)
            throws SQLException, ReflectiveOperationException {

        List<Field> fields = getUpdateFields(vo);
        PreparedStatement stmt = conn.prepareStatement(sql);

        // 设置更新字段值
        for (int i = 0; i < fields.size(); i++) {
            Field field = fields.get(i);
            field.setAccessible(true);
            stmt.setObject(i + 1, field.get(vo));
        }

        // 设置WHERE条件（主键）
        Field pkField = findField(vo.getClass(), vo.getPrimarykeyName());
        pkField.setAccessible(true);
        stmt.setObject(fields.size() + 1, pkField.get(vo));

        return stmt;
    }

    private void handleGeneratedKeys(Connection conn, SuperVO vo, PreparedStatement stmt)
            throws SQLException, ReflectiveOperationException {

        String pkField = vo.getPrimarykeyName();
        Field field = findField(vo.getClass(), pkField);
        if (field == null) return;

        Object currentValue = field.get(vo);
        if (currentValue != null) return;

        try (ResultSet rs = stmt.getGeneratedKeys()) {
            if (rs.next()) {
                Object generatedKey = rs.getObject(1);
                field.setAccessible(true);
                field.set(vo, convertType(generatedKey, field.getType()));
            }
        }
    }

    private void setParentId(SuperVO vo, Object parentId){
        if (parentId == null) return;

        String parentField = vo.getParentName();
        if (parentField == null) return;

        Field field = null;
        try {
            field = findField(vo.getClass(), parentField);
            if (field == null) return;

            field.setAccessible(true);
            field.set(vo, convertType(parentId, field.getType()));
        } catch (NoSuchFieldException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }

    }

    private boolean isNewEntity(SuperVO vo) {
        Field pkField = null;
        try {
            pkField = findField(vo.getClass(), vo.getPrimarykeyName());
            pkField.setAccessible(true);
            return pkField.get(vo) == null;
        } catch (NoSuchFieldException | IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }

    private SuperVO createTempVO(Class<? extends SuperVO> clazz)
            throws ReflectiveOperationException {
        return clazz.getDeclaredConstructor().newInstance();
    }

    private Field findField(Class<?> clazz, String fieldName) throws NoSuchFieldException {
        Class<?> searchType = clazz;
        while (searchType != null && searchType != Object.class)  {
            try {
                Field field = searchType.getDeclaredField(fieldName);
                field.setAccessible(true);  // 确保字段可访问
                return field;
            } catch (NoSuchFieldException ex) {
                searchType = searchType.getSuperclass();
            }
        }
        throw new NoSuchFieldException("Field " + fieldName + " not found in " + clazz);
    }

    private Object convertType(Object value, Class<?> targetType) {
        if (value == null) return null;
        if (targetType.isAssignableFrom(value.getClass()))  {
            return value;
        }
        // 处理常见类型转换
        if (targetType == Integer.class  || targetType == int.class)  {
            return ((Number) value).intValue();
        } else if (targetType == Long.class  || targetType == long.class)  {
            return ((Number) value).longValue();
        } else if (targetType == String.class)  {
            return value.toString();
        }else if (targetType == Boolean.class)  {
            return value == null || "N".equals(value.toString()) ? Boolean.FALSE : Boolean.TRUE;
        }else if (targetType == Double.class)  {
            return ((Number) value).doubleValue();
        }else if (targetType == Date.class)  {
            try {
                return value == null ? null : new SimpleDateFormat("yyyy-MM-dd HH:ss:mm").parse((String) value);
            } catch (ParseException e) {
                throw new RuntimeException(e);
            }
        }
        return value; // 无法转换时返回原值
    }

    private void rollbackQuietly(Connection conn) {
        try {
            if (conn != null) conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void closeQuietly(Connection conn) {
        try {
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 保留原有工具方法实现（因篇幅未完全展开）
    private Connection getConn() throws SQLException {
        return MLocator.getInstance(ConnectionProvider.class).getConnection();
    }
}